package com.tisson.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.tisson.database.FormatUtil;
import com.tisson.database.StatementUtil;
import com.tisson.pojo.Area;

public class AreaDAO {
	private String sqlStr;

	public void save(Area area) {
		try {
			if (area != null) {
				sqlStr = "";
				sqlStr += "INSERT INTO ac_area(AreaName,AreaLocation,AreaComment) VALUES(";
				sqlStr += "'" + area.getAreaName() + "',";
				sqlStr += "'" + area.getAreaLocation() + "',";
				sqlStr += "'" + area.getAreaComment() + "')";
				Statement stmt = StatementUtil.getStatement();
				stmt.executeUpdate(sqlStr);
				stmt.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List combineQuery(String name, String addr) throws SQLException {
		List result = new ArrayList();
		if ((addr == null || addr.equals(""))
				&& (name == null || name.equals(""))) {
			sqlStr = "select * from ac_area";
		} else if (addr == null || addr.equals("")) {
			sqlStr = "select * from ac_area where AreaName = '" + name + "'";
		} else if (name == null || name.equals("")) {
			sqlStr = "select * from ac_area where AreaLocation = '" + addr
					+ "'";
		} else {
			sqlStr = "select * from ac_area where AreaName = '" + name
					+ "' and AreaLocation = '" + addr + "'";
		}
		Statement stmt = StatementUtil.getStatement();
		ResultSet rs = stmt.executeQuery(sqlStr);
		while (rs.next()) {
			Area area = new Area();
			area.setAreaId(FormatUtil.ConvertUuid(rs.getBytes("AreaId")));
			area.setAreaLocation((String) rs.getObject("AreaLocation"));
			area.setAreaName((String) rs.getObject("AreaName"));
			area.setAreaComment((String) rs.getObject("AreaComment"));
			result.add(area);
		}
		rs.close();
		stmt.close();
		return result;
	}

	public Area findById(String id) {
		Area area = new Area();
		sqlStr = "";
		sqlStr += "SELECT * FROM ac_area ";
		sqlStr += "WHERE AreaId = '" + id + "'";
		try {
			Statement stmt = StatementUtil.getStatement();
			ResultSet rs = stmt.executeQuery(sqlStr);
			while (rs.next()) {
				area.setAreaId(FormatUtil.ConvertUuid(rs.getBytes("AreaId")));
				area.setAreaLocation((String) rs.getObject("AreaLocation"));
				area.setAreaName((String) rs.getObject("AreaName"));
				area.setAreaComment((String) rs.getObject("AreaComment"));
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return area;
	}

	public List findById(List<String> id) {
		ResultSet rs;
		List result = new ArrayList();
		if (id != null && id.size() > 0) {
			sqlStr = "";
			sqlStr += "SELECT * FROM ac_area ";
			sqlStr += "WHERE AreaId in (";
			sqlStr += "'" + id.get(0) + "'";
			for (int i = 1; i < id.size(); i++) {
				sqlStr += ",'" + id.get(i) + "'";
			}
			sqlStr += ")";
			try {
				Statement stmt = StatementUtil.getStatement();
				rs = stmt.executeQuery(sqlStr);
				while (rs.next()) {
					Area area = new Area();
					area.setAreaId(FormatUtil
							.ConvertUuid(rs.getBytes("AreaId")));
					area.setAreaLocation((String) rs.getObject("AreaLocation"));
					area.setAreaName((String) rs.getObject("AreaName"));
					area.setAreaComment((String) rs.getObject("AreaComment"));
					result.add(area);
				}
				rs.close();
				stmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public void update(Area area) {
		sqlStr = "";
		sqlStr += "UPDATE ac_area SET";
		sqlStr += " AreaName = '" + area.getAreaName() + "',";
		sqlStr += " AreaLocation = '" + area.getAreaLocation() + "',";
		sqlStr += " AreaComment = '" + area.getAreaComment() + "'";
		sqlStr += " WHERE AreaId = ";
		sqlStr += "'" + area.getAreaId() + "'";
		try {
			Statement stmt = StatementUtil.getStatement();
			stmt.executeUpdate(sqlStr);
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void delete(List<String> id) {
		if (id != null && id.size() > 0) {
			sqlStr = "";
			sqlStr += "DELETE FROM ac_area ";
			sqlStr += "WHERE AreaId in (";
			sqlStr += "'" + id.get(0) + "'";
			for (int i = 1; i < id.size(); i++) {
				sqlStr += ",'" + id.get(i) + "'";
			}
			sqlStr += ")";
			try {
				Statement stmt = StatementUtil.getStatement();
				stmt.executeUpdate(sqlStr);
				stmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public List search(Area areas, List<String> logic) {
		ResultSet rs;
		List result = new ArrayList();
		sqlStr = "SELECT * FROM ac_area WHERE ";
		if (areas.getAreaId() != null) {
			sqlStr += "AreaId LIKE '%" + areas.getAreaId() + "%' ";
		}
		sqlStr += logic.get(0) + " ";
		if (areas.getAreaLocation() != null) {
			sqlStr += "AreaLocation LIKE '%" + areas.getAreaLocation() + "%' ";
		}
		sqlStr += logic.get(1) + " ";
		if (areas.getAreaName() != null) {
			sqlStr += "AreaName LIKE '%" + areas.getAreaName() + "%'";
		}
		try {
			Statement stmt = StatementUtil.getStatement();
			rs = stmt.executeQuery(sqlStr);
			while (rs.next()) {
				Area area = new Area();
				area.setAreaId(FormatUtil.ConvertUuid(rs.getBytes("AreaId")));
				area.setAreaLocation((String) rs.getObject("AreaLocation"));
				area.setAreaName((String) rs.getObject("AreaName"));
				area.setAreaComment((String) rs.getObject("AreaComment"));
				result.add(area);
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	public List findAll() {
		ResultSet rs;
		List result = new ArrayList();
		sqlStr = "SELECT * FROM ac_area";
		try {
			Statement stmt = StatementUtil.getStatement();
			rs = stmt.executeQuery(sqlStr);
			while (rs.next()) {
				Area area = new Area();
				area.setAreaId(FormatUtil.ConvertUuid(rs.getBytes("AreaId")));
				area.setAreaLocation((String) rs.getObject("AreaLocation"));
				area.setAreaName((String) rs.getObject("AreaName"));
				area.setAreaComment((String) rs.getObject("AreaComment"));
				result.add(area);
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
}
